package com.ww.word.db;

import java.io.File;
import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import com.ww.core.util.Logger;
import com.ww.word.entity.Word;
import com.ww.word.ui.Constants;

public class WordDbHelper extends SQLiteOpenHelper {

	private SQLiteDatabase sqlitedb;
	private static final String T_WORD = "T_WORD";
	private static final String ID_WORD = "ID_WORD", NAME_WORD = "NAME_WORD", SIMPLE_GROUP = "SIMPLE_GROUP",
			GROUP = "WORD_GROUP", EXPLAIN = "EXPLAIN", SIMPLE_EXPLAIN = "SIMPLE_EXPLAIN", PINYIN = "PINYIN";
	private Context context;

	/**
	 * @param context Context
	 */
	public WordDbHelper(Context context) {
		super(context, Constants.DATABASE_PATH + Constants.DATABASE_NAME, null, 1);
		this.context = context;
		File dir = new File(Constants.DATABASE_PATH);
		if (!dir.exists()) {
			dir.mkdir();
		}

		SQLiteDatabase.openOrCreateDatabase(Constants.DATABASE_PATH + Constants.DATABASE_NAME, null);
		sqlitedb = getWritableDatabase();
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		db.execSQL(createWordTable());

	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
	}

	/**
	 * 创建汉字表
	 * @return  sql
	 */
	private String createWordTable() {
		StringBuffer sql1 = new StringBuffer("CREATE TABLE  IF NOT EXISTS " + T_WORD
				+ "(id INTEGER PRIMARY KEY AUTOINCREMENT ");
		sql1.append("," + ID_WORD + " INTEGER");
		sql1.append("," + NAME_WORD + " text");
		sql1.append("," + SIMPLE_GROUP + " text");
		sql1.append("," + GROUP + " text");
		sql1.append("," + EXPLAIN + " text");
		sql1.append("," + SIMPLE_EXPLAIN + " text");
		sql1.append("," + PINYIN + " text");
		sql1.append(" )");
		return sql1.toString();
	}

	/**
	 * 初始化汉字信息(不包括详情).
	 * @param words 汉字列表
	 */
	public void saveWord(List<Word> words) {
		for (int i = 0; i < words.size(); i++) {
			Word line = words.get(i);
			try {
				ContentValues cvD = new ContentValues();
				cvD.put(ID_WORD, line.getId());
				cvD.put(NAME_WORD, line.getName());
				if (checkExsit(T_WORD, line.getId())) {
					sqlitedb.update(T_WORD, cvD, ID_WORD + "=?", new String[] { line.getId() + "" });
				} else {
					sqlitedb.insert(T_WORD, null, cvD);
				}
			} catch (Exception e) {
				e.printStackTrace();
			}
		}

		Logger.info("saveWord:" + words.size());
		sqlitedb.close();
	}

	/**
	 * 更新汉字详情信息.
	 * @param word Word
	 */
	public void updateWord(Word word) {
		try {
			ContentValues cvD = new ContentValues();
			cvD.put(SIMPLE_GROUP, word.getSimple_group());
			cvD.put(PINYIN, word.getPinyin());
			cvD.put(GROUP, word.getGroup());
			cvD.put(EXPLAIN, word.getExplain());
			cvD.put(SIMPLE_EXPLAIN, word.getSimple_explain());
			if (checkExsit(T_WORD, word.getId())) {
				if (!sqlitedb.isOpen()) {
					sqlitedb = getWritableDatabase();
				}
				sqlitedb.update(T_WORD, cvD, ID_WORD + "=?", new String[] { word.getId() + "" });
			}

		} catch (Exception e) {
			e.printStackTrace();
		}
		sqlitedb.close();
	}

	/**
	 * 删除学习信息.
	 */
	public void deleteAll() {
		try {
			if (!sqlitedb.isOpen()) {
				sqlitedb = getWritableDatabase();
			}
			sqlitedb.delete(T_WORD, null, null);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			sqlitedb.close();
		}
	}

	/**
	 * 判断汉字表是否为空.	
	 * @return  boolean (true:空 false:不空)
	 */
	public boolean checkEmpty() {
		boolean bool = false;
		Cursor cur = sqlitedb.rawQuery("SELECT * FROM " + T_WORD, null);
		if (cur != null) {
			bool = cur.getCount() == 0;
		}
		if (cur != null) {
			cur.close();
		}
		sqlitedb.close();
		return bool;
	}

	/**
	 * 查询是否存在.	
	 * @param table 表 
	 * @param id 关键字
	 * @return  boolean  (true:存在 false:不存在)
	 */
	private boolean checkExsit(String table, int id) {
		boolean bool = false;
		Cursor cur = sqlitedb.rawQuery("SELECT * FROM " + table + " WHERE " + ID_WORD + "=" + id + "", null);
		if (cur != null) {
			bool = cur.getCount() > 0;
		}
		if (cur != null) {
			cur.close();
		}
		return bool;
	}

	/**
	 * 获取汉字列表.
	 * @return List<Word>
	 */
	public List<Word> getWordList() {
		List<Word> list = new ArrayList<Word>();
		Cursor cur = null;
		try {
			StudyDbHelper s = new StudyDbHelper(context);
			String last = s.getLastStudyWord();
			String sql = "SELECT * FROM " + T_WORD + " WHERE " + ID_WORD + ">" + last + " order by " + ID_WORD
					+ " asc  limit 0,20";
			if (!sqlitedb.isOpen()) {
				sqlitedb = getWritableDatabase();
			}

			cur = sqlitedb.rawQuery(sql, null);
			if (cur != null) {
				while (cur.moveToNext()) {
					list.add(makeWord(cur));
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (cur != null) {
				cur.close();
			}
			sqlitedb.close();
		}
		return list;
	}

	/**
	 * 获取汉字列表.
	 * @return List<Word>
	 */
	public List<Word> getWordAll() {
		List<Word> list = new ArrayList<Word>();
		Cursor cur = null;
		try {
			String sql = "SELECT * FROM " + T_WORD + " WHERE " + SIMPLE_GROUP + " is null";
			if (!sqlitedb.isOpen()) {
				sqlitedb = getWritableDatabase();
			}

			cur = sqlitedb.rawQuery(sql, null);
			if (cur != null) {
				while (cur.moveToNext()) {
					list.add(makeWord(cur));
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (cur != null) {
				cur.close();
			}
			sqlitedb.close();
		}
		return list;
	}

	/**
	 * 获取汉字个数.
	 * @return int
	 */
	public int getWordCount() {
		int count = 0;
		Cursor cur = sqlitedb.rawQuery("SELECT * FROM " + T_WORD, null);
		if (cur != null) {
			count = cur.getCount();
			cur.close();
		}
		return count;
	}

	/**
	 * 获取汉字详情.
	 * @param idWord 汉字id
	 * @return Word
	 */
	public Word getWord(int idWord) {
		Word d = null;
		Cursor cur = null;
		try {
			String sql = "SELECT * FROM " + T_WORD + " WHERE " + ID_WORD + "=?";
			if (!sqlitedb.isOpen()) {
				sqlitedb = getWritableDatabase();
			}
			cur = sqlitedb.rawQuery(sql, new String[] { idWord + "" });
			if (cur != null) {
				while (cur.moveToNext()) {
					d = makeWord(cur);
					d.setGroup(cur.getString(4) == null ? "" : cur.getString(4));
					d.setSimple_explain(cur.getString(5) == null ? "" : cur.getString(5));
					d.setExplain(cur.getString(6) == null ? "" : cur.getString(6));
				}
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (cur != null) {
				cur.close();
			}
			sqlitedb.close();
		}
		return d;
	}

	private Word makeWord(Cursor cur) {
		Word d = new Word();
		d.setId(cur.getInt(1));
		d.setName(cur.getString(2));
		d.setSimple_group(cur.getString(3) == null ? "" : cur.getString(3));
		//		d.setGroup(cur.getString(4) == null ? "" : cur.getString(4));
		//		d.setSimple_explain(cur.getString(5) == null ? "" : cur.getString(5));
		//		d.setExplain(cur.getString(6) == null ? "" : cur.getString(6));
		d.setTime("");
		d.setPinyin(cur.getString(7) == null ? "" : cur.getString(7));
		return d;
	}

}
